<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL常用语法 | 小马过河</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/img/manong.jpg">
    <script src="https://js.cdn.aliyun.dcloud.net.cn/dev/uni-app/uni.webview.1.5.2.js"></script>
    <link rel="manifest" href="/manifest.json">
    <link rel="apple-touch-icon" href="/img/mxx-152-152.png">
    <meta name="description" content="本章为小马学习专栏 —— mysql数据库语法">
    <meta name="keywords" content="马新想 小马学习 数据库 mysql">
    <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
    <meta name="theme-color" content="#ededed">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="msapplication-TileColor" content="#000000">
    <meta name="msapplication-TileImage" content="/img/mxx-144.png">
    
    <link rel="preload" href="/assets/css/0.styles.154256f1.css" as="style"><link rel="preload" href="/assets/js/app.f1a8d465.js" as="script"><link rel="preload" href="/assets/js/2.5bab2b7d.js" as="script"><link rel="preload" href="/assets/js/18.73488ae7.js" as="script"><link rel="prefetch" href="/assets/js/10.586b05b4.js"><link rel="prefetch" href="/assets/js/100.17640669.js"><link rel="prefetch" href="/assets/js/101.9c523894.js"><link rel="prefetch" href="/assets/js/102.27347aba.js"><link rel="prefetch" href="/assets/js/103.37a029fe.js"><link rel="prefetch" href="/assets/js/104.1d8f728c.js"><link rel="prefetch" href="/assets/js/105.8081ec6e.js"><link rel="prefetch" href="/assets/js/11.28ad280e.js"><link rel="prefetch" href="/assets/js/12.5e108f27.js"><link rel="prefetch" href="/assets/js/13.3678126c.js"><link rel="prefetch" href="/assets/js/14.505a08b7.js"><link rel="prefetch" href="/assets/js/15.8f3c0c53.js"><link rel="prefetch" href="/assets/js/16.4256e6cb.js"><link rel="prefetch" href="/assets/js/17.17d3d968.js"><link rel="prefetch" href="/assets/js/19.9d056309.js"><link rel="prefetch" href="/assets/js/20.52185694.js"><link rel="prefetch" href="/assets/js/21.3b7e8437.js"><link rel="prefetch" href="/assets/js/22.4d4d8a8b.js"><link rel="prefetch" href="/assets/js/23.dac1a360.js"><link rel="prefetch" href="/assets/js/24.798b5147.js"><link rel="prefetch" href="/assets/js/25.16d30679.js"><link rel="prefetch" href="/assets/js/26.3e5e50ab.js"><link rel="prefetch" href="/assets/js/27.99e01b84.js"><link rel="prefetch" href="/assets/js/28.86b7c539.js"><link rel="prefetch" href="/assets/js/29.ea981f89.js"><link rel="prefetch" href="/assets/js/3.c22f362a.js"><link rel="prefetch" href="/assets/js/30.6c923d43.js"><link rel="prefetch" href="/assets/js/31.df940781.js"><link rel="prefetch" href="/assets/js/32.3e5a19c7.js"><link rel="prefetch" href="/assets/js/33.0936aa71.js"><link rel="prefetch" href="/assets/js/34.566820dc.js"><link rel="prefetch" href="/assets/js/35.e263f3e4.js"><link rel="prefetch" href="/assets/js/36.32ae1d3b.js"><link rel="prefetch" href="/assets/js/37.ac0fe1ce.js"><link rel="prefetch" href="/assets/js/38.5ff1b8eb.js"><link rel="prefetch" href="/assets/js/39.da39237f.js"><link rel="prefetch" href="/assets/js/4.dca68745.js"><link rel="prefetch" href="/assets/js/40.c92cf280.js"><link rel="prefetch" href="/assets/js/41.b2318f21.js"><link rel="prefetch" href="/assets/js/42.9e98acc0.js"><link rel="prefetch" href="/assets/js/43.6a890842.js"><link rel="prefetch" href="/assets/js/44.8c501675.js"><link rel="prefetch" href="/assets/js/45.a7b73149.js"><link rel="prefetch" href="/assets/js/46.08c4dcbb.js"><link rel="prefetch" href="/assets/js/47.0fa3a317.js"><link rel="prefetch" href="/assets/js/48.15f53959.js"><link rel="prefetch" href="/assets/js/49.f9b62975.js"><link rel="prefetch" href="/assets/js/5.49ddc4eb.js"><link rel="prefetch" href="/assets/js/50.5d678fce.js"><link rel="prefetch" href="/assets/js/51.f07100da.js"><link rel="prefetch" href="/assets/js/52.25782c95.js"><link rel="prefetch" href="/assets/js/53.1151ca9a.js"><link rel="prefetch" href="/assets/js/54.86132d90.js"><link rel="prefetch" href="/assets/js/55.5e273c50.js"><link rel="prefetch" href="/assets/js/56.94d89ae7.js"><link rel="prefetch" href="/assets/js/57.ff4f6ce5.js"><link rel="prefetch" href="/assets/js/58.d6bba7bf.js"><link rel="prefetch" href="/assets/js/59.fd39aa8f.js"><link rel="prefetch" href="/assets/js/6.73ba3b93.js"><link rel="prefetch" href="/assets/js/60.3b547209.js"><link rel="prefetch" href="/assets/js/61.c2746621.js"><link rel="prefetch" href="/assets/js/62.c7246830.js"><link rel="prefetch" href="/assets/js/63.8cf018aa.js"><link rel="prefetch" href="/assets/js/64.5982f68e.js"><link rel="prefetch" href="/assets/js/65.6a323f0b.js"><link rel="prefetch" href="/assets/js/66.df6d599d.js"><link rel="prefetch" href="/assets/js/67.5c26c24c.js"><link rel="prefetch" href="/assets/js/68.e53dceea.js"><link rel="prefetch" href="/assets/js/69.65ee4851.js"><link rel="prefetch" href="/assets/js/7.541920c5.js"><link rel="prefetch" href="/assets/js/70.4bfafea4.js"><link rel="prefetch" href="/assets/js/71.b4f496a8.js"><link rel="prefetch" href="/assets/js/72.1b1ffea6.js"><link rel="prefetch" href="/assets/js/73.8929e1c4.js"><link rel="prefetch" href="/assets/js/74.d3c5fcad.js"><link rel="prefetch" href="/assets/js/75.9d01a456.js"><link rel="prefetch" href="/assets/js/76.3149b796.js"><link rel="prefetch" href="/assets/js/77.3abe6bd4.js"><link rel="prefetch" href="/assets/js/78.ae477312.js"><link rel="prefetch" href="/assets/js/79.00837d52.js"><link rel="prefetch" href="/assets/js/8.a50175f7.js"><link rel="prefetch" href="/assets/js/80.d626622b.js"><link rel="prefetch" href="/assets/js/81.ad2df06d.js"><link rel="prefetch" href="/assets/js/82.9ac0d88a.js"><link rel="prefetch" href="/assets/js/83.b23219b4.js"><link rel="prefetch" href="/assets/js/84.55f2741f.js"><link rel="prefetch" href="/assets/js/85.33b715e5.js"><link rel="prefetch" href="/assets/js/86.85aea4fa.js"><link rel="prefetch" href="/assets/js/87.6fd2f3fa.js"><link rel="prefetch" href="/assets/js/88.a9ea00b4.js"><link rel="prefetch" href="/assets/js/89.7e58e472.js"><link rel="prefetch" href="/assets/js/9.83acbe8c.js"><link rel="prefetch" href="/assets/js/90.e15687d5.js"><link rel="prefetch" href="/assets/js/91.026b108a.js"><link rel="prefetch" href="/assets/js/92.840afd12.js"><link rel="prefetch" href="/assets/js/93.00439ef2.js"><link rel="prefetch" href="/assets/js/94.6c994a63.js"><link rel="prefetch" href="/assets/js/95.e3dd6367.js"><link rel="prefetch" href="/assets/js/96.cbe1207c.js"><link rel="prefetch" href="/assets/js/97.34584059.js"><link rel="prefetch" href="/assets/js/98.9b2de21d.js"><link rel="prefetch" href="/assets/js/99.95d4993f.js">
    <link rel="stylesheet" href="/assets/css/0.styles.154256f1.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="layout-wrapper"><!----> <div class="theme-container"><header class="navbar ele-navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/img/manong.jpg" alt="小马过河" class="logo"> <span class="site-name can-hide">小马过河</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><!----> <a href="/" class="nav-link">
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="导航" class="dropdown-title"><span class="title">导航</span> <span class="arrow down"></span></button> <button type="button" aria-label="导航" class="mobile-dropdown-title"><span class="title">导航</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/nav/nav.html" class="nav-link">
  常用站点
</a></li><li class="dropdown-item"><!----> <a href="/nav/web.html" class="nav-link">
  前端站点
</a></li><li class="dropdown-item"><!----> <a href="/nav/afterEnd.html" class="nav-link">
  后端站点
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><span class="title">前端</span> <span class="arrow down"></span></button> <button type="button" aria-label="前端" class="mobile-dropdown-title"><span class="title">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          CSS+HTML系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/csshtml/css-formatContent.html" class="nav-link">
  CSS格式化上下文
</a></li><li class="dropdown-subitem"><a href="/web/csshtml/css-bfc.html" class="nav-link">
  CSS的BFC
</a></li></ul></li><li class="dropdown-item"><h4>
          JS系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/js/js-prototype.html" class="nav-link">
  JS-原型与原型链
</a></li><li class="dropdown-subitem"><a href="/web/js/js-context.html" class="nav-link">
  JS-执行上下文
</a></li></ul></li><li class="dropdown-item"><h4>
          Vue2系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/vue/mvvm.html" class="nav-link">
  Vue响应式
</a></li></ul></li><li class="dropdown-item"><h4>
          常见面试题
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/question/css.html" class="nav-link">
  CSS面试题
</a></li><li class="dropdown-subitem"><a href="/web/question/vue.html" class="nav-link">
  Vue2.x面试题
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Java" class="dropdown-title"><span class="title">Java</span> <span class="arrow down"></span></button> <button type="button" aria-label="Java" class="mobile-dropdown-title"><span class="title">Java</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          基础系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/basics/know.html" class="nav-link">
  Java初识
</a></li><li class="dropdown-subitem"><a href="/java/basics/number.html" class="nav-link">
  Java数字
</a></li><li class="dropdown-subitem"><a href="/java/basics/string.html" class="nav-link">
  Java字符串
</a></li></ul></li><li class="dropdown-item"><h4>
          集合
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/collection/collection.html" class="nav-link">
  Collection集合
</a></li><li class="dropdown-subitem"><a href="/java/collection/list.html" class="nav-link">
  List集合
</a></li></ul></li><li class="dropdown-item"><h4>
          IO操作
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/io/" class="nav-link">
  IO初识
</a></li></ul></li><li class="dropdown-item"><h4>
          网络编程
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/network/ip.html" class="nav-link">
  IP操作
</a></li></ul></li><li class="dropdown-item"><h4>
          JDBC操作
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/jdbc/mysql-connect.html" class="nav-link">
  MySQL操作
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><span class="title">数据库</span> <span class="arrow down"></span></button> <button type="button" aria-label="数据库" class="mobile-dropdown-title"><span class="title">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          MySQL系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/database/mysql/init.html" aria-current="page" class="nav-link router-link-exact-active router-link-active">
  常用语法
</a></li><li class="dropdown-subitem"><a href="/database/mysql/data-type.html" class="nav-link">
  数据类型
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="设计模式" class="dropdown-title"><span class="title">设计模式</span> <span class="arrow down"></span></button> <button type="button" aria-label="设计模式" class="mobile-dropdown-title"><span class="title">设计模式</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/designMode/observer.html" class="nav-link">
  观察者模式
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="运维/工具" class="dropdown-title"><span class="title">运维/工具</span> <span class="arrow down"></span></button> <button type="button" aria-label="运维/工具" class="mobile-dropdown-title"><span class="title">运维/工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          Maven
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/tools/maven/" class="nav-link">
  初识
</a></li></ul></li><li class="dropdown-item"><h4>
          Tomcat
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/tools/tomcat/" class="nav-link">
  初识
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="English" class="dropdown-title"><span class="title">English</span> <span class="arrow down"></span></button> <button type="button" aria-label="English" class="mobile-dropdown-title"><span class="title">English</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          英语读音
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/pronunciation/phonogram.html" class="nav-link">
  音标
</a></li></ul></li><li class="dropdown-item"><h4>
          英语单词
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/word/call.html" class="nav-link">
  电话
</a></li><li class="dropdown-subitem"><a href="/english/word/daily-life.html" class="nav-link">
  日常生活
</a></li><li class="dropdown-subitem"><a href="/english/word/animal.html" class="nav-link">
  动物
</a></li></ul></li><li class="dropdown-item"><h4>
          英语语法
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/grammar/sentence.html" class="nav-link">
  句子成分
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Study" class="dropdown-title"><span class="title">Study</span> <span class="arrow down"></span></button> <button type="button" aria-label="Study" class="mobile-dropdown-title"><span class="title">Study</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/study/computers/" class="nav-link">
  计算机基础
</a></li><li class="dropdown-item"><!----> <a href="/study/web/" class="nav-link">
  前端知识路线
</a></li><li class="dropdown-item"><!----> <a href="/study/java/" class="nav-link">
  Java知识路线
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Self" class="dropdown-title"><span class="title">Self</span> <span class="arrow down"></span></button> <button type="button" aria-label="Self" class="mobile-dropdown-title"><span class="title">Self</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/self/work/" class="nav-link">
  工作
</a></li></ul></div> <!----></div> <!----></nav> <div class="loginItem"><!----></div></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><!----> <a href="/" class="nav-link">
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="导航" class="dropdown-title"><span class="title">导航</span> <span class="arrow down"></span></button> <button type="button" aria-label="导航" class="mobile-dropdown-title"><span class="title">导航</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/nav/nav.html" class="nav-link">
  常用站点
</a></li><li class="dropdown-item"><!----> <a href="/nav/web.html" class="nav-link">
  前端站点
</a></li><li class="dropdown-item"><!----> <a href="/nav/afterEnd.html" class="nav-link">
  后端站点
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><span class="title">前端</span> <span class="arrow down"></span></button> <button type="button" aria-label="前端" class="mobile-dropdown-title"><span class="title">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          CSS+HTML系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/csshtml/css-formatContent.html" class="nav-link">
  CSS格式化上下文
</a></li><li class="dropdown-subitem"><a href="/web/csshtml/css-bfc.html" class="nav-link">
  CSS的BFC
</a></li></ul></li><li class="dropdown-item"><h4>
          JS系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/js/js-prototype.html" class="nav-link">
  JS-原型与原型链
</a></li><li class="dropdown-subitem"><a href="/web/js/js-context.html" class="nav-link">
  JS-执行上下文
</a></li></ul></li><li class="dropdown-item"><h4>
          Vue2系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/vue/mvvm.html" class="nav-link">
  Vue响应式
</a></li></ul></li><li class="dropdown-item"><h4>
          常见面试题
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/web/question/css.html" class="nav-link">
  CSS面试题
</a></li><li class="dropdown-subitem"><a href="/web/question/vue.html" class="nav-link">
  Vue2.x面试题
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Java" class="dropdown-title"><span class="title">Java</span> <span class="arrow down"></span></button> <button type="button" aria-label="Java" class="mobile-dropdown-title"><span class="title">Java</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          基础系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/basics/know.html" class="nav-link">
  Java初识
</a></li><li class="dropdown-subitem"><a href="/java/basics/number.html" class="nav-link">
  Java数字
</a></li><li class="dropdown-subitem"><a href="/java/basics/string.html" class="nav-link">
  Java字符串
</a></li></ul></li><li class="dropdown-item"><h4>
          集合
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/collection/collection.html" class="nav-link">
  Collection集合
</a></li><li class="dropdown-subitem"><a href="/java/collection/list.html" class="nav-link">
  List集合
</a></li></ul></li><li class="dropdown-item"><h4>
          IO操作
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/io/" class="nav-link">
  IO初识
</a></li></ul></li><li class="dropdown-item"><h4>
          网络编程
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/network/ip.html" class="nav-link">
  IP操作
</a></li></ul></li><li class="dropdown-item"><h4>
          JDBC操作
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/java/jdbc/mysql-connect.html" class="nav-link">
  MySQL操作
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="数据库" class="dropdown-title"><span class="title">数据库</span> <span class="arrow down"></span></button> <button type="button" aria-label="数据库" class="mobile-dropdown-title"><span class="title">数据库</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          MySQL系列
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/database/mysql/init.html" aria-current="page" class="nav-link router-link-exact-active router-link-active">
  常用语法
</a></li><li class="dropdown-subitem"><a href="/database/mysql/data-type.html" class="nav-link">
  数据类型
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="设计模式" class="dropdown-title"><span class="title">设计模式</span> <span class="arrow down"></span></button> <button type="button" aria-label="设计模式" class="mobile-dropdown-title"><span class="title">设计模式</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/designMode/observer.html" class="nav-link">
  观察者模式
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="运维/工具" class="dropdown-title"><span class="title">运维/工具</span> <span class="arrow down"></span></button> <button type="button" aria-label="运维/工具" class="mobile-dropdown-title"><span class="title">运维/工具</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          Maven
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/tools/maven/" class="nav-link">
  初识
</a></li></ul></li><li class="dropdown-item"><h4>
          Tomcat
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/tools/tomcat/" class="nav-link">
  初识
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="English" class="dropdown-title"><span class="title">English</span> <span class="arrow down"></span></button> <button type="button" aria-label="English" class="mobile-dropdown-title"><span class="title">English</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>
          英语读音
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/pronunciation/phonogram.html" class="nav-link">
  音标
</a></li></ul></li><li class="dropdown-item"><h4>
          英语单词
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/word/call.html" class="nav-link">
  电话
</a></li><li class="dropdown-subitem"><a href="/english/word/daily-life.html" class="nav-link">
  日常生活
</a></li><li class="dropdown-subitem"><a href="/english/word/animal.html" class="nav-link">
  动物
</a></li></ul></li><li class="dropdown-item"><h4>
          英语语法
        </h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/english/grammar/sentence.html" class="nav-link">
  句子成分
</a></li></ul></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Study" class="dropdown-title"><span class="title">Study</span> <span class="arrow down"></span></button> <button type="button" aria-label="Study" class="mobile-dropdown-title"><span class="title">Study</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/study/computers/" class="nav-link">
  计算机基础
</a></li><li class="dropdown-item"><!----> <a href="/study/web/" class="nav-link">
  前端知识路线
</a></li><li class="dropdown-item"><!----> <a href="/study/java/" class="nav-link">
  Java知识路线
</a></li></ul></div> <!----></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="Self" class="dropdown-title"><span class="title">Self</span> <span class="arrow down"></span></button> <button type="button" aria-label="Self" class="mobile-dropdown-title"><span class="title">Self</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/self/work/" class="nav-link">
  工作
</a></li></ul></div> <!----></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySQL系列</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/database/mysql/init.html" aria-current="page" class="active sidebar-link">MySQL常用语法</a></li><li><a href="/database/mysql/data-type.html" class="sidebar-link">MySQL数据类型</a></li></ul></section></li></ul> </aside> <main class="page"> <div class="main"><div class="content"><div class="theme-default-content content__default"><h1 id="mysql常用语法"><a href="#mysql常用语法" class="header-anchor">#</a> MySQL常用语法</h1> <p></p><div class="table-of-contents"><ul><li><a href="#系统操作">系统操作</a></li><li><a href="#数据库操作">数据库操作</a><ul><li><a href="#创建数据库">创建数据库</a></li><li><a href="#删除数据库">删除数据库</a></li><li><a href="#修改数据库">修改数据库</a></li><li><a href="#显示数据库">显示数据库</a></li><li><a href="#选择数据库">选择数据库</a></li><li><a href="#查看数据库状态">查看数据库状态</a></li></ul></li><li><a href="#数据表操作">数据表操作</a><ul><li><a href="#创建数据表">创建数据表</a></li><li><a href="#添加数据表字段">添加数据表字段</a></li><li><a href="#删除数据表">删除数据表</a></li><li><a href="#删除数据表字段">删除数据表字段</a></li><li><a href="#更改数据表字段">更改数据表字段</a></li><li><a href="#查看数据表">查看数据表</a></li><li><a href="#显示数据表">显示数据表</a></li></ul></li><li><a href="#数据操作">数据操作</a><ul><li><a href="#插入数据">插入数据</a></li><li><a href="#删除数据">删除数据</a></li><li><a href="#修改数据">修改数据</a></li></ul></li><li><a href="#查询操作">查询操作</a><ul><li><a href="#查询">查询</a></li><li><a href="#where条件查询">where条件查询</a></li><li><a href="#like模糊查询">like模糊查询</a></li><li><a href="#排序-order-by">排序 ORDER BY</a></li><li><a href="#合并-union-all">合并 union （all)</a></li></ul></li><li><a href="#mysql字段描述">MySQL字段描述</a></li></ul></div><p></p> <p>🐴</p> <p><strong>数据库版本：<code>8.0.17</code></strong></p> <h2 id="系统操作"><a href="#系统操作" class="header-anchor">#</a> 系统操作</h2> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 登录</span>
mysql <span class="token operator">-</span>u root <span class="token operator">-</span>p

<span class="token comment">// 设置密码</span>
<span class="token keyword">set</span> password <span class="token keyword">for</span> root<span class="token variable">@localhost</span><span class="token operator">=</span>password<span class="token punctuation">(</span><span class="token string">'新密码'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> <span class="token punctuation">(</span>方法一<span class="token punctuation">)</span>
<span class="token keyword">alter</span> <span class="token keyword">user</span> <span class="token keyword">user</span><span class="token punctuation">(</span><span class="token punctuation">)</span> identified <span class="token keyword">by</span> <span class="token string">'新密码'</span><span class="token punctuation">;</span> <span class="token punctuation">(</span>方法二<span class="token punctuation">)</span>

<span class="token comment">// 设置密码时使用加密方式</span>
<span class="token keyword">alter</span> <span class="token keyword">user</span> <span class="token string">'root'</span><span class="token variable">@'localhost'</span> identified  <span class="token keyword">with</span> mysql_native_password <span class="token keyword">by</span>  <span class="token string">'密码'</span><span class="token punctuation">;</span>

<span class="token comment">// 刷新权限</span>
flush <span class="token keyword">privileges</span>
</code></pre></div> <h2 id="数据库操作"><a href="#数据库操作" class="header-anchor">#</a> 数据库操作</h2> <h3 id="创建数据库"><a href="#创建数据库" class="header-anchor">#</a> 创建数据库</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 创建数据库</span>
<span class="token keyword">create</span> <span class="token keyword">database</span> 数据库名<span class="token punctuation">;</span> 

<span class="token comment">// 创建数据库并设置uff8编码</span>
<span class="token keyword">create</span> <span class="token keyword">database</span> 数据库名 <span class="token keyword">default</span> <span class="token keyword">character</span> <span class="token keyword">set</span> utf8 <span class="token keyword">collate</span> utf8_general_ci<span class="token punctuation">;</span> 
</code></pre></div><h3 id="删除数据库"><a href="#删除数据库" class="header-anchor">#</a> 删除数据库</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 删除数据库</span>
<span class="token keyword">drop</span> <span class="token keyword">database</span> 数据库名
</code></pre></div><h3 id="修改数据库"><a href="#修改数据库" class="header-anchor">#</a> 修改数据库</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 修改数据库字符编码</span>
<span class="token keyword">use</span> 数据库名<span class="token punctuation">;</span> <span class="token comment">//进入要修改的数据库</span>
<span class="token keyword">alter</span> <span class="token keyword">database</span> <span class="token keyword">character</span> <span class="token keyword">set</span> utf8<span class="token punctuation">;</span>
</code></pre></div><h3 id="显示数据库"><a href="#显示数据库" class="header-anchor">#</a> 显示数据库</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 显示数据库</span>
<span class="token keyword">show</span> <span class="token keyword">databases</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="选择数据库"><a href="#选择数据库" class="header-anchor">#</a> 选择数据库</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 选择数据库</span>
<span class="token keyword">use</span>  数据库名<span class="token punctuation">;</span>
</code></pre></div><h3 id="查看数据库状态"><a href="#查看数据库状态" class="header-anchor">#</a> 查看数据库状态</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 查看数据库状态</span>
<span class="token keyword">status</span><span class="token punctuation">;</span>
</code></pre></div> <h2 id="数据表操作"><a href="#数据表操作" class="header-anchor">#</a> 数据表操作</h2> <h3 id="创建数据表"><a href="#创建数据表" class="header-anchor">#</a> 创建数据表</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//显示数据表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> people<span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  age <span class="token keyword">INT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="添加数据表字段"><a href="#添加数据表字段" class="header-anchor">#</a> 添加数据表字段</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//添加数据表字段 设置数据类型 + 非空 + 注释</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">comment</span> <span class="token string">'应用访问地址'</span><span class="token punctuation">;</span> 


<span class="token comment">// 增加自增主键</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span><span class="token keyword">add</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="删除数据表"><a href="#删除数据表" class="header-anchor">#</a> 删除数据表</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//删除数据表</span>
<span class="token keyword">drop</span> <span class="token keyword">table</span> 表名<span class="token punctuation">;</span>

<span class="token comment">// 判断表是否存在，存在先删除</span>
<span class="token keyword">drop</span> tabel <span class="token keyword">if</span> <span class="token keyword">exists</span> 表名<span class="token punctuation">;</span>
</code></pre></div><h3 id="删除数据表字段"><a href="#删除数据表字段" class="header-anchor">#</a> 删除数据表字段</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//删除数据表字段</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">drop</span> 字段名
</code></pre></div><h3 id="更改数据表字段"><a href="#更改数据表字段" class="header-anchor">#</a> 更改数据表字段</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 更改字段名</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 change name name_change <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span>

<span class="token comment">//更改数据表字段类型</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 字段名  <span class="token keyword">modify</span> <span class="token keyword">column</span> app_name <span class="token keyword">text</span><span class="token punctuation">;</span>

<span class="token comment">// 更改表注释</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 字段名 <span class="token keyword">comment</span> <span class="token string">'系统信息表'</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="查看数据表"><a href="#查看数据表" class="header-anchor">#</a> 查看数据表</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//查看数据表结构</span>
<span class="token keyword">describe</span> 表名<span class="token punctuation">;</span> 

<span class="token comment">//可以简写</span>
<span class="token keyword">desc</span> 表名<span class="token punctuation">;</span>
</code></pre></div><h3 id="显示数据表"><a href="#显示数据表" class="header-anchor">#</a> 显示数据表</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//显示数据表</span>
<span class="token keyword">show</span> <span class="token keyword">tables</span><span class="token punctuation">;</span>
</code></pre></div> <h2 id="数据操作"><a href="#数据操作" class="header-anchor">#</a> 数据操作</h2> <h3 id="插入数据"><a href="#插入数据" class="header-anchor">#</a> 插入数据</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//插入数据</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> 表名 <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">,</span><span class="token string">'小明'</span><span class="token punctuation">,</span><span class="token number">23</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="删除数据"><a href="#删除数据" class="header-anchor">#</a> 删除数据</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 删除id为5的用户</span>
<span class="token keyword">delete</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">5</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="修改数据"><a href="#修改数据" class="header-anchor">#</a> 修改数据</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//修改id为4的用户name值为小明</span>
<span class="token keyword">update</span> 表名 <span class="token keyword">set</span> name<span class="token operator">=</span><span class="token string">'小明'</span> <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">4</span><span class="token punctuation">;</span>
</code></pre></div> <h2 id="查询操作"><a href="#查询操作" class="header-anchor">#</a> 查询操作</h2> <h3 id="查询"><a href="#查询" class="header-anchor">#</a> 查询</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 查询所有数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>

<span class="token comment">// 查询name age字段的数据</span>
<span class="token keyword">select</span> name<span class="token punctuation">,</span>age <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>

<span class="token comment">// 使用as给查询的字段起别名</span>
<span class="token keyword">select</span> name <span class="token keyword">as</span> new_name<span class="token punctuation">,</span> age <span class="token keyword">as</span> new_age <span class="token keyword">from</span> 表名<span class="token punctuation">;</span>

<span class="token comment">// 使用as 给表起别名</span>
<span class="token keyword">select</span> p<span class="token punctuation">.</span>name<span class="token punctuation">,</span> p<span class="token punctuation">.</span>age <span class="token keyword">from</span> people <span class="token keyword">as</span> p<span class="token punctuation">;</span>
</code></pre></div><h3 id="where条件查询"><a href="#where条件查询" class="header-anchor">#</a> where条件查询</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 选择id为1的数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> id<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">// 选择id大于1的数据 </span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> id<span class="token operator">&gt;</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token comment">//选择名字叫小王的并且年龄23的数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> name<span class="token operator">=</span><span class="token string">&quot;小王&quot;</span> <span class="token operator">and</span> age<span class="token operator">=</span><span class="token number">23</span><span class="token punctuation">;</span>
</code></pre></div><p><strong>在使用where条件查询语句中，可以使用的操作符：</strong></p> <ul><li><code>=</code> 等于号</li> <li><code>&lt;&gt;, !=</code> 不等于，检测两个值是否相等，如果不相等返回true</li> <li><code>&gt;=</code> 大于等于号</li> <li><code>&lt;=</code> 小于等于号</li> <li><code>&gt;</code> 大于号</li> <li><code>&lt;</code> 小于号</li> <li><code>and</code> 并且符（and的优先级大于or）</li> <li><code>or</code> 或者符</li></ul> <h3 id="like模糊查询"><a href="#like模糊查询" class="header-anchor">#</a> like模糊查询</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 查询name字段中含有k字母的数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> name <span class="token operator">like</span> <span class="token string">'%k%'</span><span class="token punctuation">;</span>

<span class="token comment">// 类似正则 [] 模糊查询 ,匹配 张测试 马测试 刘测试</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> name <span class="token operator">like</span> <span class="token string">'[张马刘]测试'</span><span class="token punctuation">;</span>

<span class="token comment">// 匹配非 张测试 马测试 刘测试得数据</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> name <span class="token operator">like</span> <span class="token string">'[^张马刘]测试'</span>
</code></pre></div><p><strong><code>like</code>模糊匹配经常与<code>%</code>和<code>_</code>一起使用：</strong></p> <ul><li><code>%m</code>：以m结尾的数据</li> <li><code>m%</code>：以m开头的数据</li> <li><code>%m%</code>：含有m的数据</li> <li><code>_m_</code>：三位且中间字母是m的</li> <li><code>_m</code>：两位且结尾字母是m的</li> <li><code>m_</code>：两位且开头字母是m的</li></ul> <h3 id="排序-order-by"><a href="#排序-order-by" class="header-anchor">#</a> 排序 ORDER BY</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//正序 数值递增，字母自然顺序（a-z）</span>
<span class="token keyword">select</span> name <span class="token keyword">from</span> 表名 <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">asc</span><span class="token punctuation">;</span>

<span class="token comment">//倒序 数值递减， 字母反序</span>
<span class="token keyword">select</span> name <span class="token keyword">from</span> 表名 <span class="token keyword">order</span> <span class="token keyword">by</span> <span class="token keyword">desc</span><span class="token punctuation">;</span>

<span class="token comment">// 单个name字段排序</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">order</span> <span class="token keyword">by</span> name <span class="token keyword">desc</span><span class="token punctuation">;</span>

<span class="token comment">// 条件筛选后排序</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> 表名 <span class="token keyword">where</span> <span class="token keyword">time</span><span class="token operator">&gt;</span><span class="token number">5000</span> <span class="token keyword">order</span> <span class="token keyword">by</span> name <span class="token keyword">desc</span><span class="token punctuation">;</span>
</code></pre></div><h3 id="合并-union-all"><a href="#合并-union-all" class="header-anchor">#</a> 合并 union （all)</h3> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 返回表名1和表名2合并后的数据 不包含重复（并默认排序）</span>
<span class="token keyword">select</span> name <span class="token keyword">from</span> 表名<span class="token number">1</span>  <span class="token keyword">union</span> <span class="token keyword">select</span> name <span class="token keyword">from</span> 表名<span class="token number">2</span>

<span class="token comment">// 返回表名1和表名2合并后的数据 包含重复（默认不排序）</span>
<span class="token keyword">select</span> name <span class="token keyword">from</span> 表名<span class="token number">1</span>  <span class="token keyword">union</span> <span class="token keyword">all</span> <span class="token keyword">select</span> name <span class="token keyword">from</span> 表名<span class="token number">2</span>
</code></pre></div> <h2 id="mysql字段描述"><a href="#mysql字段描述" class="header-anchor">#</a> MySQL字段描述</h2> <ul><li><strong>主键约束：<code>primary key</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 添加主键约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token keyword">add</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>字段名<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre></div><ul><li><strong>自增标志 <code>auto_increment</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 添加自增标志</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">auto_increment</span><span class="token punctuation">;</span>
</code></pre></div><ul><li><strong>外键约束：<code>foreign key</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 添加外键约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 子表名 <span class="token keyword">add</span> <span class="token keyword">foreign</span> <span class="token keyword">key</span><span class="token punctuation">(</span>子表的外键名<span class="token punctuation">)</span> <span class="token keyword">references</span> 父表名（父表主键名） <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">option</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">option</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
</code></pre></div><p>外键约束中 <code>option</code>选项可为：</p> <ul><li><code>cscade</code>: 从父表中删除或者更新对应的行,当前子表同时删除或者更新对应的行</li> <li><code>set null</code>: 从父表中删除或者更新对应行,当前子表同时将外键列设置为<code>NULL</code>,如果子表外键字段设置了<code>NOT NULL</code>,还需要设置<code>DEFAULT</code>,否则会出错</li> <li><code>no action</code>: <code>InnoDB</code>拒绝删除或者更新父表,对父表的外键字段拒绝操作,这意味着删除父表中的行也会失败</li> <li><code>restrict</code>: 拒绝删除或者更新父表</li> <li><code>set default</code>: <code>InnoDB</code>目前不支持。</li></ul> <p>外键约束常用的选项：</p> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//父表更新同时更新子表,父表删除的时候如果子表不存在对应的数据删除成功,如果存在对应数据,删除失败.</span>
<span class="token keyword">on</span> <span class="token keyword">update</span> cscade <span class="token keyword">on</span> <span class="token keyword">delete</span> <span class="token keyword">restrict</span>

<span class="token comment">//父表更新同时更新子表,父表删除子表的数据也删除.</span>
<span class="token keyword">on</span> <span class="token keyword">update</span> cscade <span class="token keyword">on</span> <span class="token keyword">delete</span> cscade 
</code></pre></div><ul><li><strong>唯一约束 <code>unique</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">// 添加唯一约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span>  <span class="token keyword">unique</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>字段名<span class="token punctuation">`</span><span class="token punctuation">)</span>
</code></pre></div><ul><li><strong>非空约束：<code>not null</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token comment">//添加非空约束</span>
<span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token operator">not</span> <span class="token boolean">null</span>
</code></pre></div><ul><li><strong>默认值：<code>default</code></strong></li></ul> <div class="language-sql extra-class"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> 表名 <span class="token keyword">add</span> 字段名 <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">default</span> <span class="token string">'值'</span>
</code></pre></div></div> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">最近更新时间:</span> <span class="time">7/2/2021, 11:27:27 AM</span></div></footer> <div class="page-nav"><p class="inner"><!----> <span class="next"><a href="/database/mysql/data-type.html">
        MySQL数据类型
      </a>
      →
    </span></p></div></div> <div class="floatSilder hidden-sm-and-down"><div class="floatSilderWrapper" data-v-4100c7a9><div class="topTitle" data-v-4100c7a9>MySQL常用语法</div> <div class="contentNav" data-v-4100c7a9><ul class="nav" data-v-4100c7a9><li class="title" data-v-4100c7a9><a href="#系统操作" data-v-4100c7a9>系统操作</a></li><li class="title" data-v-4100c7a9><a href="#数据库操作" data-v-4100c7a9>数据库操作</a></li><li class="subTitle" data-v-4100c7a9><a href="#创建数据库" data-v-4100c7a9>创建数据库</a></li><li class="subTitle" data-v-4100c7a9><a href="#删除数据库" data-v-4100c7a9>删除数据库</a></li><li class="subTitle" data-v-4100c7a9><a href="#修改数据库" data-v-4100c7a9>修改数据库</a></li><li class="subTitle" data-v-4100c7a9><a href="#显示数据库" data-v-4100c7a9>显示数据库</a></li><li class="subTitle" data-v-4100c7a9><a href="#选择数据库" data-v-4100c7a9>选择数据库</a></li><li class="subTitle" data-v-4100c7a9><a href="#查看数据库状态" data-v-4100c7a9>查看数据库状态</a></li><li class="title" data-v-4100c7a9><a href="#数据表操作" data-v-4100c7a9>数据表操作</a></li><li class="subTitle" data-v-4100c7a9><a href="#创建数据表" data-v-4100c7a9>创建数据表</a></li><li class="subTitle" data-v-4100c7a9><a href="#添加数据表字段" data-v-4100c7a9>添加数据表字段</a></li><li class="subTitle" data-v-4100c7a9><a href="#删除数据表" data-v-4100c7a9>删除数据表</a></li><li class="subTitle" data-v-4100c7a9><a href="#删除数据表字段" data-v-4100c7a9>删除数据表字段</a></li><li class="subTitle" data-v-4100c7a9><a href="#更改数据表字段" data-v-4100c7a9>更改数据表字段</a></li><li class="subTitle" data-v-4100c7a9><a href="#查看数据表" data-v-4100c7a9>查看数据表</a></li><li class="subTitle" data-v-4100c7a9><a href="#显示数据表" data-v-4100c7a9>显示数据表</a></li><li class="title" data-v-4100c7a9><a href="#数据操作" data-v-4100c7a9>数据操作</a></li><li class="subTitle" data-v-4100c7a9><a href="#插入数据" data-v-4100c7a9>插入数据</a></li><li class="subTitle" data-v-4100c7a9><a href="#删除数据" data-v-4100c7a9>删除数据</a></li><li class="subTitle" data-v-4100c7a9><a href="#修改数据" data-v-4100c7a9>修改数据</a></li><li class="title" data-v-4100c7a9><a href="#查询操作" data-v-4100c7a9>查询操作</a></li><li class="subTitle" data-v-4100c7a9><a href="#查询" data-v-4100c7a9>查询</a></li><li class="subTitle" data-v-4100c7a9><a href="#where条件查询" data-v-4100c7a9>where条件查询</a></li><li class="subTitle" data-v-4100c7a9><a href="#like模糊查询" data-v-4100c7a9>like模糊查询</a></li><li class="subTitle" data-v-4100c7a9><a href="#排序-order-by" data-v-4100c7a9>排序 ORDER BY</a></li><li class="subTitle" data-v-4100c7a9><a href="#合并-union-all" data-v-4100c7a9>合并 union （all)</a></li><li class="title" data-v-4100c7a9><a href="#mysql字段描述" data-v-4100c7a9>MySQL字段描述</a></li></ul></div></div></div></div> </main></div></div><div class="global-ui"><!----></div></div>
    <script src="/assets/js/app.f1a8d465.js" defer></script><script src="/assets/js/2.5bab2b7d.js" defer></script><script src="/assets/js/18.73488ae7.js" defer></script>
  </body>
</html>
